package com.mi.easyexceldemo.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Type;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtil {
    public static Workbook getWorkbook(InputStream is) throws IOException, InvalidFormatException {
        return WorkbookFactory.create(is);
    }
    public static String getCellValues(Cell cell) {
        if (cell == null) {
            return null;
        }
        CellType cellType = cell.getCellTypeEnum();
        String cellValue = null;
        switch (cellType) {
            //文本
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            //数字
            case NUMERIC:
                cellValue = NumberToTextConverter.toText(cell.getNumericCellValue()) ;
                break;
            //布尔型
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            //公式
            case FORMULA:
                try {
                    cellValue = String.valueOf(cell.getStringCellValue());
                } catch (IllegalStateException e) {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            default:
                break;
        }
        return cellValue;
    }
    private static Object getCellValue(Cell cell) {
        Object value = null;
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字

        switch (cell.getCellTypeEnum()) {
            case STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case NUMERIC:
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                    value = sdf.format(cell.getDateCellValue());
                } else {
                    value = df2.format(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case BLANK:
                value = "";
                break;
            case _NONE:
                value = "";
                break;
            case ERROR:
                value = "";
                break;
            case FORMULA:
                value = "";
                break;
            default:
                break;
        }
        return value;
    }
    public static List<List<String>> getExcelData(String path) throws IOException, InvalidFormatException {
        Workbook wb = getWorkbook(new FileInputStream(path));
        Sheet sheet = wb.getSheetAt(0);
        int rowNum = sheet.getLastRowNum();
        List<List<String>> all = new ArrayList<List<String>>();
        for (int i = 1; i <= rowNum; i++){
            Row cells = sheet.getRow(i);
            List<String> rowList = new ArrayList<String >();
            int colNum = cells.getLastCellNum();
            for (int j = 0; j < colNum; j++){
                Cell cell = cells.getCell(j);
                if (cell == null) {
                    rowList.add("");
                    continue;
                }
                String cellValue = String.valueOf(getCellValue(cell));
                rowList.add(cellValue);
            }
            if (rowList != null && rowList.size() > 0){
                all.add(rowList);
            }
        }
        return all;
    }
    public static int getImportExcelColumnCount(String path)
            throws IOException, InvalidFormatException {
        Workbook wb = getWorkbook(new FileInputStream(path));
        Sheet sheet = wb.getSheetAt(0);
        int rowNum = sheet.getLastRowNum();
        if (rowNum > 0) {
            Row cells = sheet.getRow(0);
            return cells.getLastCellNum();
        }
        return 0;
    }
    public static Object convertToObject(final Object obj, final String fieldName, String strValue){
        Type type = ReflectionUtils.getAccessibleField(obj, fieldName).getGenericType();
        Object value = strValue;
        if (null != strValue) {
            switch (type.getTypeName()) {
                case "java.lang.Integer": value = StringUtils.isBlank(strValue) ? 0 : Integer.parseInt(strValue); break;
                case "java.lang.Long":  value = StringUtils.isBlank(strValue) ? 0 : Long.parseLong(strValue); break;
                case "java.lang.Float": value = StringUtils.isBlank(strValue) ? 0 : Float.parseFloat(strValue); break;
                case "java.lang.Double": value = StringUtils.isBlank(strValue) ? 0 : Double.parseDouble(strValue); break;
                case "java.lang.Boolean":
                    if (StringUtils.equalsAnyIgnoreCase(strValue, "是", "true", "1")) {
                        value = true;
                    } else if (StringUtils.equalsAnyIgnoreCase(strValue, "否", "false", "0")) {
                        value = false;
                    }else {
                        value = null;
                    }
                    break;
                case "java.util.Date": value = StringUtils.isBlank(strValue) ? null: DateStringUtil.getFormatDate(strValue,null); break;
                default: break;
            }
        }
        return value;
    }
}
